import sqlite3
import json
import os
import sys

if getattr(sys, 'frozen', False):
    BASE_DIR = os.path.dirname(sys.executable)
else:
    BASE_DIR = os.path.dirname(__file__)

DB_PATH = os.path.join(BASE_DIR, 'history.db')

def init_db():
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute('''
        CREATE TABLE IF NOT EXISTS history (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            time TEXT,
            remark TEXT,
            params TEXT,
            data TEXT,
            channel_order TEXT,
            com_port TEXT
        )
    ''')
    # 为已存在的表添加channel_order列（如果不存在）
    try:
        c.execute('ALTER TABLE history ADD COLUMN channel_order TEXT')
    except sqlite3.OperationalError:
        pass  # 列已存在
    # 为已存在的表添加com_port列（如果不存在）
    try:
        c.execute('ALTER TABLE history ADD COLUMN com_port TEXT')
    except sqlite3.OperationalError:
        pass  # 列已存在
    conn.commit()
    conn.close()

def add_record(time, remark, params, data, channel_order=None, com_port=None):
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    channel_order_json = json.dumps(channel_order) if channel_order else None
    c.execute('INSERT INTO history (time, remark, params, data, channel_order, com_port) VALUES (?, ?, ?, ?, ?, ?)',
              (time, remark, json.dumps(params, ensure_ascii=False), json.dumps(data), channel_order_json, com_port))
    conn.commit()
    conn.close()

def get_all_records():
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute('SELECT id, time, remark, params, data, channel_order, com_port FROM history ORDER BY id DESC')
    rows = c.fetchall()
    conn.close()
    records = []
    for row in rows:
        # 处理channel_order字段，保持向后兼容
        channel_order = None
        if len(row) > 5 and row[5]:
            try:
                channel_order = json.loads(row[5])
            except:
                channel_order = None
        
        # 处理com_port字段，保持向后兼容
        com_port = None
        if len(row) > 6 and row[6]:
            com_port = row[6]
        
        records.append({
            'id': row[0],
            'time': row[1],
            'remark': row[2],
            'params': json.loads(row[3]),
            'data': json.loads(row[4]),
            'channel_order': channel_order,
            'com_port': com_port
        })
    return records

def update_remark(record_id, remark):
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute('UPDATE history SET remark=? WHERE id=?', (remark, record_id))
    conn.commit()
    conn.close()

def delete_record(record_id):
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute('DELETE FROM history WHERE id=?', (record_id,))
    conn.commit()
    conn.close()

def update_data(record_id, data):
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute('UPDATE history SET data=? WHERE id=?', (json.dumps(data), record_id))
    conn.commit()
    conn.close()